﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.Text;

namespace GetWechatArticle
{
    public delegate void AddText(string text, Color color);

    /// <summary>
    /// 数据操作
    /// </summary>
    public class DAL
    {
        public static AddText addText;

        #region 表说明

        // users表：用于记录获取文章的公众号，并且记录上一次获取时间
        // biz：公众号编号，是经过base64编码的
        // biznumber：公众号编号对应的base64解码后的内容，为一串数字
        // dataList：记录文章列表信息对应的表，规则为：'A'+biznumber+'articleList'
        // nickname：公众号名
        // lastdate：上次获取的时间

        // "A" + biznumber + "articleList"表：用于记录某个公众号的文章列表等信息
        // 公众号的文件有两种：第一种是直接在列表页显示内容，不需要进入页面查看（使用：times，contents存放相应内容，type为0）；第二种是普通的文章，在列表页显示标题、图片、简介，需要点击后查看详细内容（使用：title、digest、url存放相应内容，type为1）
        // times：发布时间           这两项针对第一种公众号文章
        // contents：发布的内容      这两项针对第一种公众号文章

        // title：标题               这三项针对第二种公众号文章
        // digest：内容简要          这三项针对第二种公众号文章
        // url：文章链接地址         这三项针对第二种公众号文章

        // status：0为默认，刚写入文章；1-2为生成错误，3为多次重试还是错误，9为被禁文章，10为已经生成HTML
        // type：0为单独内容；1为普通文章

        #endregion

        /// <summary>
        /// 添加公众号信息及对应的表
        /// </summary>
        /// <param name="biz">公众号编号</param>
        /// <param name="nickname">公众号名</param>
        /// <returns></returns>
        public static bool AddUserTable(string biz, string nickname)
        {
            bool flag = true;
            try
            {
                string biznumber = Base64.Base64Decode(biz);
                if (!OP.RegIsMatch(Config.intPat, biznumber))
                    return false;
                string dataList = "A" + biznumber + "articleList";

                StringBuilder strSql = new StringBuilder(200);
                //查询当前公众号编号是否已存在，不存在则入库
                strSql.Append("select COUNT(0) FROM users where biz=@biz");
                SQLiteParameter[] parameters = { new SQLiteParameter("@biz", DbType.String, 200) };
                parameters[0].Value = biz;
                int o = int.Parse(DbHelperSQLite.GetSingle(strSql.ToString(), parameters).ToString());
                if (o == 0)
                {
                    strSql.Clear();
                    strSql = new StringBuilder(200);
                    strSql.Append("insert into [users](");
                    strSql.Append("biz,biznumber,dataList,nickname)");
                    strSql.Append(" values (");
                    strSql.Append("@biz,@biznumber,@dataList,@nickname)");
                    parameters = new SQLiteParameter[] {
                        new SQLiteParameter("@biz",DbType.String,200),
                        new SQLiteParameter("@biznumber", DbType.String,20),
                        new SQLiteParameter("@dataList", DbType.String,50),
                        new SQLiteParameter("@nickname", DbType.String,200)};
                    parameters[0].Value = biz;
                    parameters[1].Value = biznumber;
                    parameters[2].Value = dataList;
                    parameters[3].Value = nickname;
                    DbHelperSQLite.ExecuteSql(strSql.ToString(), parameters);
                }

                strSql.Clear();
                //查询当前公众号对应的数据表是否存在，不存在则创建对应的表
                strSql.Append("SELECT count(0) FROM sqlite_master WHERE type='table' AND name=@name");
                parameters = new SQLiteParameter[] { new SQLiteParameter("@name", DbType.String, 200) };
                parameters[0].Value = dataList;
                o = int.Parse(DbHelperSQLite.GetSingle(strSql.ToString(), parameters).ToString());
                if (o == 0)
                {
                    strSql.Clear();
                    strSql = new StringBuilder(200);
                    strSql.Append("CREATE TABLE [" + dataList + "]([id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[times] VARCHAR(20),[contents] TEXT,[title] VARCHAR(1000),[digest] VARCHAR(2000),[url] VARCHAR(4000),[status] INT DEFAULT 0,[type] INT DEFAULT 0);");
                    DbHelperSQLite.ExecuteSql(strSql.ToString());
                }
            }
            catch (Exception ex)
            {
                flag = false;
            }
            return flag;
        }

        /// <summary>
        /// 获取当前公众号上一次获取文章的时间
        /// </summary>
        /// <param name="biz">公众号编号</param>
        /// <returns></returns>
        public static string GetLastDate(string biz)
        {
            string lastdate = "2000-01-01 00:00:00";
            try
            {
                StringBuilder strSql = new StringBuilder(200);
                strSql.Append("select lastdate FROM [users] where biz=@biz limit 1");
                SQLiteParameter[] parameters = { new SQLiteParameter("@biz", DbType.String, 200) };
                parameters[0].Value = biz;

                object o = DbHelperSQLite.GetSingle(strSql.ToString(), parameters);
                if (o != null)
                {
                    if (OP.RegIsMatch(Config.DateTimePat, o.ToString()))
                    {
                        lastdate = o.ToString();
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return lastdate;
        }

        /// <summary>
        /// 更新最后获取时间
        /// </summary>
        /// <param name="biz">公众号编号</param>
        /// <returns></returns>
        public static bool UpdateComplate(string biz)
        {
            bool flag = true;

            try
            {
                StringBuilder strSql = new StringBuilder(200);
                strSql = new StringBuilder(200);
                strSql.Append("update [users] ");
                strSql.Append(" set lastdate=@lastdate ");
                strSql.Append(" where biz=@biz");
                SQLiteParameter[] parameters = {
                    new SQLiteParameter("@lastdate",DbType.String,20),
                    new SQLiteParameter("@biz", DbType.String,200)};
                parameters[0].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                parameters[1].Value = biz;
                DbHelperSQLite.ExecuteSql(strSql.ToString(), parameters);
            }
            catch (Exception ex)
            {
                flag = false;
            }

            return flag;
        }

        /// <summary>
        /// 获取某文章的内容信息
        /// </summary>
        /// <param name="biz">公众号编号</param>
        /// <returns></returns>
        public static Article GetArticleUrl(string biz)
        {
            Article a = new Article();

            string biznumber = Base64.Base64Decode(biz);
            if (!OP.RegIsMatch(Config.intPat, biznumber))
            {
                a.rtnFlag = false;
                a.rtnStr = "biz非法";
                return null;
            }

            string table = "A" + biznumber + "articleList";
            StringBuilder strSql = new StringBuilder(200);
            strSql.Append("select id,times,contents,url,status,title,type FROM [" + table + "] where status<=2 order by id desc limit 1");

            SQLiteDataReader sdr = DbHelperSQLite.ExecuteReader(strSql.ToString());
            if (sdr.HasRows)
            {
                sdr.Read();
                a.id = int.Parse(sdr["id"].ToString());
                a.times = sdr["times"].ToString();
                a.contents = sdr["contents"].ToString();
                a.url = sdr["url"].ToString();
                a.status = int.Parse(sdr["status"].ToString());
                a.title = sdr["title"].ToString();
                a.type = int.Parse(sdr["type"].ToString());
            }
            else
            {
                a.rtnStr = "没有记录";
            }
            sdr.Close();

            return a;
        }

        /// <summary>
        /// 设置文章状态，标记某文章已经生成成功或被禁删除等情况
        /// </summary>
        /// <param name="biz">公众号编号</param>
        /// <param name="id">文章ID</param>
        /// <param name="status">状态</param>
        /// <returns></returns>
        public static bool SetArticleStatus(string biz, int id, int status)
        {
            bool flag = true;
            string biznumber = Base64.Base64Decode(biz);
            if (!OP.RegIsMatch(Config.intPat, biznumber))
                return false;
            string table = "A" + biznumber + "articleList";

            try
            {
                StringBuilder strSql = new StringBuilder(200);
                strSql = new StringBuilder(200);
                strSql.Append("update [" + table + "] ");
                if (status == 1)
                    strSql.Append(" set status=status+@status ");
                else
                    strSql.Append(" set status=@status ");
                strSql.Append(" where id=@id");
                SQLiteParameter[] parameters = {
                    new SQLiteParameter("@status", DbType.Int32),
                    new SQLiteParameter("@id", DbType.Int32)
                };
                parameters[0].Value = status;
                parameters[1].Value = id;
                int i = DbHelperSQLite.ExecuteSql(strSql.ToString(), parameters);
                if (i == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                flag = false;
            }

            return flag;
        }

        /// <summary>
        /// 分析返回的文章列表内容
        /// </summary>
        /// <param name="biz">公众号编号</param>
        /// <param name="html">文章列表内容</param>
        public static void AnalyzeData(string biz, string html)
        {
            string[,] arr = OP.RegExpFindArray(Config.comm_msg_info_and_app_msg_ext_infoPat, html);
            for (int i = 0; i < arr.GetLength(0); i++)
            {
                string dates = arr[i, 0];
                string contents = arr[i, 1];
                string titles = arr[i, 2];
                string digests = arr[i, 3];
                string content_urls = arr[i, 4];

                //判断是否是直接内容，还是普通文章
                int type = string.IsNullOrWhiteSpace(digests) ? 0 : 1;

                if (OP.RegIsMatch(Config.datetimeIntPat, dates))
                    dates = OP.ConvertIntToDateTime10(double.Parse(dates)).ToString("yyyy-MM-dd HH:mm:ss");

                if (titles.Length > 0 && content_urls.Length > 10)
                {
                    content_urls = content_urls.Replace(@"\/", "/");

                    var rtnStr = InsertListDatabase(biz, dates, contents, titles, digests, content_urls, type);

                    addText(rtnStr, Color.Black);
                }
            }
        }

        /// <summary>
        /// 写入文章列表到数据表中
        /// </summary>
        /// <param name="biz">公众号编号</param>
        /// <param name="times">发布时间</param>
        /// <param name="contents">发布的内容</param>
        /// <param name="title">标题</param>
        /// <param name="digest">内容简要</param>
        /// <param name="url">文章链接地址</param>
        /// <param name="type">0为直接内容；1为普通文章</param>
        /// <returns></returns>
        public static string InsertListDatabase(string biz, string times, string contents, string title, string digest, string url, int type)
        {
            string rtnStr = "";
            string biznumber = Base64.Base64Decode(biz);
            if (!OP.RegIsMatch(Config.intPat, biznumber))
                rtnStr = "biz错误";

            try
            {
                string dataList = "A" + biznumber + "articleList";
                StringBuilder strSql;
                SQLiteParameter[] parameters;
                bool flagTimes = false;
                bool flagUrl = false;

                if (type == 0)
                {
                    strSql = new StringBuilder(200);
                    strSql.Append("select COUNT(0) FROM " + dataList + " where times=@times");
                    parameters = new SQLiteParameter[] { new SQLiteParameter("@times", DbType.String, 200) };
                    parameters[0].Value = times;

                    flagTimes = DbHelperSQLite.Exists(strSql.ToString(), parameters);
                    if (flagTimes)
                    {
                        rtnStr = "存在相同的times：" + times;
                    }

                }
                else if (type == 1)
                {
                    strSql = new StringBuilder(200);
                    strSql.Append("select COUNT(0) FROM " + dataList + " where url=@url");
                    parameters = new SQLiteParameter[] { new SQLiteParameter("@url", DbType.String, 200) };
                    parameters[0].Value = url;

                    flagUrl = DbHelperSQLite.Exists(strSql.ToString(), parameters);
                    if (flagUrl)
                    {
                        rtnStr = "存在相同的url：" + url;
                    }
                }
                else
                {
                    flagTimes = true; flagUrl = true;
                }

                if (!flagTimes && !flagUrl)
                {
                    strSql = new StringBuilder(200);
                    strSql.Append("insert into [" + dataList + "](");
                    strSql.Append("times,contents,title,digest,url,status,type)");
                    strSql.Append(" values (");
                    strSql.Append("@times,@contents,@title,@digest,@url,@status,@type)");
                    parameters = new SQLiteParameter[] {
                        new SQLiteParameter("@times",DbType.String,20),
                        new SQLiteParameter("@contents", DbType.String),
                        new SQLiteParameter("@title", DbType.String,1000),
                        new SQLiteParameter("@digest", DbType.String,2000),
                        new SQLiteParameter("@url", DbType.String,4000),
                        new SQLiteParameter("@status", DbType.Int32),
                        new SQLiteParameter("@type", DbType.Int32)};
                    parameters[0].Value = times;
                    parameters[1].Value = contents;
                    parameters[2].Value = title;
                    parameters[3].Value = digest;
                    parameters[4].Value = url;
                    parameters[5].Value = 0;
                    parameters[6].Value = type;
                    DbHelperSQLite.ExecuteSql(strSql.ToString(), parameters);
                }
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }

            return rtnStr;
        }

    }
}
